/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import bean.CompanyBean;

/**
 * @author Veer
 * 
 */
public class DataAccess {

	/**
	 * @param args
	 */

	public static Connection connect() {

		try {
			Class.forName("org.hsqldb.jdbc.JDBCDriver");

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		try {
			Connection connection = DriverManager.getConnection(
					"jdbc:hsqldb:assetTracker", // filenames
					"SA", // username
					"");
			return connection;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} // password

	}

	public static void disconnect(Connection connection) throws SQLException {

		Statement st = connection.createStatement();
		st.execute("SHUTDOWN");
		connection.close(); // if there are no other open connection
	}

	public static boolean createAsset(String name, String description, String createdOn, String company_id, String aleTag) {
		// TODO Auto-generated method stub
		String expression = "INSERT INTO ALE_Table values(NEXT VALUE FOR ale_id,'"
				+ name
				+ "','"
				+ description
				+ "','"
				+ createdOn
				+ "','"+aleTag+"','"
				+ company_id
				+ "')";
		System.out.println(expression);
		try {
			Connection connection = connect();
			Statement st = null;
			st = connection.createStatement(); // statements
			int i = st.executeUpdate(expression); // run the query
			if (i == -1) {
				System.out.println("db error : " + expression);
			} else
				System.out.println("Query Successful ! ");
			st.close();
			disconnect(connection);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}

	public static boolean createCompany(String name, String address,
			String phone, String email, String date) {
		// TODO Auto-generated method stub
		String expression = "INSERT INTO COMPANY_DETAILS values(NEXT VALUE FOR company_id,'"
				+ name
				+ "','"
				+ address
				+ "','"
				+ phone
				+ "','"
				+ email
				+ "','" + date + "','y')";
		System.out.println(expression);
		try {
			Connection connection = connect();
			Statement st = null;
			st = connection.createStatement(); // statements
			int i = st.executeUpdate(expression); // run the query
			if (i == -1) {
				System.out.println("db error : " + expression);
			} else
				System.out.println("Query Successful ! ");
			st.close();
			disconnect(connection);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}

	public static ArrayList<String> fetchCompanyName() {

		String expression = "Select * from company_details";
		Connection connection = connect();
		Statement st = null;
		ResultSet rs = null;

		// do something with the result set.
		ArrayList<String> companyNameList = new ArrayList<String>();
		try {
			st = connection.createStatement(); // statement objects can be reuse
			rs = st.executeQuery(expression); // run the query

			int index = 0;
			for (; rs.next();) {
				String name = rs.getString("name");
				companyNameList.add(index, name);
				index++;
			}
			st.close();
			disconnect(connection);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return companyNameList;
	}
	
	public static ArrayList<String> fetchALEName() {

		String expression = "Select * from ale_table";
		Connection connection = connect();
		Statement st = null;
		ResultSet rs = null;

		// do something with the result set.
		ArrayList<String> ALENameList = new ArrayList<String>();
		try {
			st = connection.createStatement(); // statement objects can be reuse
			rs = st.executeQuery(expression); // run the query

			int index = 0;
			for (; rs.next();) {
				String name = rs.getString("name");
				ALENameList.add(index, name);
				index++;
			}
			st.close();
			disconnect(connection);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ALENameList;
	}


	public static ArrayList<CompanyBean> fetchCompanyDetails() {

		String company_id, name, address, phone, email = null;
		Date created_on = null;
		String flag = null;

		String expression = "Select * from company_details";
		Connection connection = connect();
		Statement st = null;
		ResultSet rs = null;

		// do something with the result set.

		ArrayList<CompanyBean> companyList = new ArrayList<CompanyBean>();
		companyList.clear();
		try {
			st = connection.createStatement(); // statement objects can be reuse
			rs = st.executeQuery(expression); // run the query
			int index = 0;
			for (; rs.next();) {
				flag = (String) rs.getString("flag");
				name = (String) rs.getString("name");
				address = (String) rs.getString("address");
				company_id = (String) rs.getString("company_id");
				email = (String) rs.getString("email");
				phone = (String) rs.getString("phone");
				created_on = (Date) rs.getDate("created_on");

				// holderBean.setFlag(flag);
				// holderBean.setName(name);
				// holderBean.setAddress(address);
				// holderBean.setCompany_id(company_id);
				// holderBean.setEmail(email);
				// holderBean.setPhone(phone);
				// holderBean.setCreated_on(created_on);
				//

				CompanyBean holderBean = new CompanyBean(company_id, name,
						address, phone, email, created_on, flag);
				System.out.println("------------");
				System.out.println(index);
				System.out.println(holderBean);
				System.out.println(name);
				if ("y".equals(flag)) {
					System.out.println("Yes");
					companyList.add(index, holderBean);
					index++;
				}
			}
			st.close();
			disconnect(connection);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		for (int i = 0; i < companyList.size(); i++) {
			System.out.println(companyList.get(i).getCompany_id());
			System.out.println(companyList.get(i).getName());
			System.out.println("-----------------------------------");
		}
		return companyList;
	}

	public static boolean createUser(String name, String password,
			String employer) {
		// TODO Auto-generated method stub
		String expression = "INSERT INTO login values('" + name + "','"
				+ password + "','" + employer + "','y')";
		System.out.println(expression);
		try {
			Connection connection = connect();
			Statement st = null;
			st = connection.createStatement(); // statements
			int i = st.executeUpdate(expression); // run the query
			if (i == -1) {
				System.out.println("db error : " + expression);
			} else
				System.out.println("Query Successful ! ");
			st.close();
			disconnect(connection);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}

	public static String login(String name, String password) {
		// TODO Auto-generated method stub
		String expression = "Select * from login where username = '" + name
				+ "' AND password ='" + password + "'";
		System.out.println(expression);
		Connection connection = connect();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = connection.createStatement(); // statement objects can be reuse
			rs = st.executeQuery(expression); // run the query

			if (rs.next()) {
				String company_id = (String) rs.getString("company_id");
				return company_id;
			} else {
				return null;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

}
